Re: [GENERAL] Typecasting datetype as date. How do I cope with NULLs? - Mailing list pgsql-general

> SR> Dear All,
> SR> For those of you who don't want to wade through the details, here's the
> SR> question: "How do I get the date portion of a datetime field for ALL
> SR> ENTRIES in a table regardless of whether the entry is NULL or not? (N.B.
> SR> Typecasting a datetime NULL as date generates an error)"
>

<snip>

>
>There are, on my mind, at least two answers. For experience I use
>small table 'create table a( dt datetime, i int)'. Hera are data in
>this table (one row has NULL as dt value):
>
>tolik=> select * from a;
>dt                          | i
>----------------------------+--
>Thu Nov 26 16:35:23 1998 MSK| 1
>Wed Nov 25 00:00:00 1998 MSK| 2
>Fri Nov 27 00:00:00 1998 MSK| 3
>                            |10
>
>First use 'union':
>-----------------------------------------------
>select dt::date, i from a where dt is not null
>union
>select NULL,     i from a where dt is null;
>      date| i
>----------+--
>11-25-1998| 2
>11-26-1998| 1
>11-27-1998| 3
>          |10
>(4 rows)

I had not thought of using a UNION, thanks for that (well that's not
strictly true, I was 'procedurally' implemeting it rather then using SQL!!)
so this is a definite possibility.
I still feel that this equates to two SQL queries since the backend will
have to process each individually and then UNION them (is this right?).  My
suggestion of:

patients=> SELECT surname,firstname,othernames,date_part('day',dob) as dd,
patients-> date_part('month',dob) as mm, date_part('year',dob) as yyyy
patients-> FROM patients;
surname|firstname|othernames          |dd|mm|yyyy
-------+---------+--------------------+--+--+----
Goose  |Mother   |Lay Golden Eggs     |11| 1|1923
One    |Un       |Uno Ein             |11|11|1111
Light  |Dee      |Full                |22| 1|1933
Rison  |Stuart   |                    |  |  |
Rison  |This     |Pal                 |  |  |
Rison  |Mark     |                    |  |  |
(6 rows)

means only one query need to be executed by the backend and it can cope
with NULL, but it 'generates' three date fields and I would like only one.

>
>Second, try use date_trunc('day', dt) instead date_part:
>--------------------------------------------------------------
>tolik=> select date_trunc('day', dt), i from a;
>date_trunc                  | i
>----------------------------+--
>Thu Nov 26 00:00:00 1998 MSK| 1
>Wed Nov 25 00:00:00 1998 MSK| 2
>Fri Nov 27 00:00:00 1998 MSK| 3
>                            |10
>(4 rows)
>
>--------------------------------------------------------------


Again, this solves one part of my problem (i.e. the query functions even if
I datetime is NULL) but what I'm looking for is something that ONLY shows
the date portion of a datetime (e.g. Thu Nov 26 16:35:23 1998 MSK becomes
26-11-1998) so the date_trunc masking is not quite what I'm looking for.

Would it be possible to do a select datetime and then output only the
dateday, datemonth and dateyear using an RE??

All the same, thanks for your suggestion Anatoly, UNION's the winner so far ;)

regards,

S.



+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+